#!/usr/bin/env python3
# encoding=utf-8

import os
import datetime
from copy import copy
from openpyxl import load_workbook
from with_win32 import Win32AppManager
from word_process import word_to_image, fill_word_with_excel_value
from commom_api import (get_excel_setting_info, is_weekend_day, check_code_in_list
                        , get_doc_dirpath, email_merge_by_product, get_chart_rules
                        , get_excel_name_by_emailinfo, excel_rename_list_update)

import logging
logger = logging.getLogger(__name__)

def find_last_row_by_xlwings(sheet):
    for lastrow in range(sheet.used_range.last_cell.row, 1, -1):
        val = sheet.range((lastrow, 1)).value
        if val == None:
            continue
        return lastrow


def filter_emailinfo_by_xlwings(emailinfo):
    newinfo = []
    if len(emailinfo) == 0:
        return newinfo
    emailinfo = sorted(emailinfo, key=lambda x: x["name"])
    emaildictinfo = email_merge_by_product(emailinfo)
    for key, maillist in emaildictinfo.items():
    # for onemail in emailinfo:
        xlslist = get_excel_name_by_emailinfo(key, withspcode=(maillist[0]['code'], maillist[0]['name']))
        for xlsname in xlslist:
            if xlsname == None:
                continue
            orgexcel = os.path.join(get_doc_dirpath(), xlsname)
            with Win32AppManager(orgexcel, opttype='xw') as (excel, wb):
                if wb == None:
                    logging.error(f"open {orgexcel} error")
                    continue
                for sheet in wb.sheets:
                    if sheet.name.find('（周）') <= 0:
                        continue
                    
                    lastrow = find_last_row_by_xlwings(sheet)
                    maxdate = sheet.range((lastrow, 1)).value
                    logger.info(f"get maxrow {xlsname}:{lastrow} is {maxdate}")
                    for onemail in maillist:
                        emaildate = datetime.datetime.strptime(onemail['date'], "%Y-%m-%d")
                        if emaildate <= maxdate:
                            logger.info(f"filter {onemail} by range {xlsname}")
                            continue
                        if onemail not in newinfo:
                            newinfo.append(onemail)
    # newinfo = sorted(newinfo, key=lambda x: x["name"])
    # newinfo = sorted(newinfo, key=lambda x: datetime.datetime.strptime(x["date"], "%Y-%m-%d"))
    return newinfo

def change_xlsinfo_by_xlwrings_sheet(sheet):
    xlsinfo = {}
    xlsbase = get_excel_setting_info()
    maxcol = sheet.used_range.last_cell.column
    for col in range(1, maxcol + 1):
        cval = sheet.range((xlsbase['headerline'], col)).value
        # print(f'col{col} is {cval}')
        if cval == None:
            logger.debug(f"col max {maxcol} {col} none data")
            break
        for key, val in xlsbase.items():
            if cval == val:
                xlsinfo[key] = (col, val)
    for key, val in xlsbase.items():
        if key in xlsinfo:
            continue
        xlsinfo[key] = val
    
    maxrow = find_last_row_by_xlwings(sheet)
    for row in range(0, maxrow):
        cval = sheet.range((maxrow - row, 1)).value
        # print(f'row{row} is {cval}')
        if cval != None:
            # logger.debug(f"row max {maxrow} {row} none data")
            break
    maxrow = maxrow - row
    xlsinfo['flexiblepos'] = [xlsbase['headerline']+1, col, maxrow]

    mindate = sheet.range((xlsbase['headerline']+1, 1)).value.replace(tzinfo=None)
    maxdate = sheet.range((maxrow, 1)).value.replace(tzinfo=None)
    xlsinfo['daterange'] = [mindate, maxdate]

    if 'keepword' not in xlsinfo:
        xlsinfo['keepword'] = []
    if 'keepexcelpng' not in xlsinfo:
        xlsinfo['keepexcelpng'] = []
    return xlsinfo

def deal_with_auto_update_lastrow_formula(sheet, lastrow):
    # =LET(
    #     ed, 177,
    #     sd, ed-15,
    #     INDIRECT(
    #         ADDRESS(
    #             IFS(
    #                 DATEDIF(INDIRECT(ADDRESS(sd,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-1,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-1
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-2,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-2
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-3,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-3
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-4,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-4
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-5,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-5
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-6,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-6
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-7,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-7
    #                 ,DATEDIF(INDIRECT(ADDRESS(sd-8,1)), INDIRECT(ADDRESS(ed,1)), "M")=1, sd-8
    #             )
    #         ,4)
    #     )	
    # )
    maxcol = sheet.used_range.last_cell.column
    for col in range(3, maxcol + 1):
        cval = sheet.range((2, col)).formula
        if not isinstance(cval, str) or not cval.startswith('='):
            continue
        cb2 = cval.replace(str(lastrow), str(lastrow+1))
        sheet.range((2, col)).formula = cb2
        logger.info(f'{cb2} for {col}')

def deal_with_xlwrings_sheet_data(wb, xlsinfo, onemail, codeinfo):
    sheet = wb.sheets[xlsinfo['sheetname']]
    flexiblecol = xlsinfo['flexiblepos'][1] + 1
    lastrow = xlsinfo['flexiblepos'][2]

    for col in range(1, flexiblecol):
        onekey = None
        for key, val in xlsinfo.items():
            try:
                if col == val[0]:
                    onekey = key
            except:
                continue
        # logger.info(onekey)
        newcell = sheet.range((lastrow+1, col))
        oldcell = sheet.range((lastrow, col))
        oldval = oldcell.formula
        if isinstance(oldval, str) and oldval.startswith('='):
            oldval = oldcell.formula
            oldval = oldval.replace(str(lastrow), str(lastrow+1))
            oldval = oldval.replace(str(lastrow-1), str(lastrow))
            newcell.formula = oldval
        else:
            oldval = oldcell.value
            if onekey != None:
                if onekey in onemail.keys():
                    if onekey == 'date':
                        # 不能用字符串，必须用时间，不然count失效
                        # newcell.value = datetime.datetime.strptime(onemail[onekey], "%Y-%m-%d")
                        newcell.value = onemail[onekey]
                        if len(xlsinfo['daterange']) != 3:
                            xlsinfo['daterange'].append(onemail[onekey])
                        else:
                            xlsinfo['daterange'][2] = onemail[onekey]
                    else:
                        newcell.value = float(onemail[onekey])
                elif onekey in codeinfo.keys():
                    # logger.info('get from code')
                    for date, val in codeinfo[onekey]:
                        if date == onemail['date']:
                            newcell.value = val
            else:
                newcell.value = oldval
        
        newcell.color = oldcell.color
        newcell.api.Font.Color = oldcell.api.Font.Color
        # newcell.api.Font.ColorIndex = oldcell.api.Font.ColorIndex
        newcell.api.NumberFormat = oldcell.api.NumberFormat
        newcell.api.VerticalAlignment = oldcell.api.VerticalAlignment
        newcell.api.HorizontalAlignment = oldcell.api.HorizontalAlignment

    cb2 = sheet.range('B2').formula.replace(str(lastrow), str(lastrow+1))
    sheet.range('B2').formula = cb2
    logger.info(f'{cb2} for %s' % onemail['date'])
    deal_with_auto_update_lastrow_formula(sheet, lastrow)
    xlsinfo['flexiblepos'][2] = lastrow + 1
    wb.save(xlsinfo['excel'])
    # wb.RefreshAll()                # 刷新数据透视表、Power Query等
    return xlsinfo

def deal_with_xlwrings_sheet_oldchart(wb, xlsinfo):
    lastrow = xlsinfo['flexiblepos'][2]
    sheet = wb.sheets[xlsinfo['sheetname']]
    for chart in sheet.charts:
        # print(chart.api[1].ChartTitle.Text)
        chart.top += 10
        # chart.left += 10
        for series in chart.api[1].SeriesCollection():
            if hasattr(series, 'Formula') and series.Formula.startswith('='):
                sernumref = series.Formula
                series.Formula = sernumref.replace(str(lastrow-1), str(lastrow))
            if hasattr(series, 'FormulaLocal') and series.FormulaLocal.startswith('='):
                sernumref = series.FormulaLocal
                series.FormulaLocal = sernumref.replace(str(lastrow-1), str(lastrow))
    wb.save(xlsinfo['excel'])
  
def export_img_from_xw_sheet(sheet, rule, pngname):
    for chartobj in sheet.charts:
        chart = chartobj.api[1]
        chart_name = chart.ChartTitle.Text
        if chart_name.find('超额') > 0:
            continue
        # print(chart_name)
        legend = []
        for idx, series in enumerate(chart.SeriesCollection()):
            if series.Name not in rule:
                continue
            # print(series.Name)
            legend.append(idx)
            series.Border.LineStyle = -4142  # 设置为无线条（隐藏折线）
            series.Border.ColorIndex = -4142  # 设置为无颜色
        for i in legend:
            chart.Legend.LegendEntries(i+1).Delete()  # 删除或设为不可见
        chartobj.to_png(pngname)

def get_img_from_xw_sheet(wb, xlsinfo):
    if 'code' not in xlsinfo:
        logger.warning(f"not chart rule code for {xlsinfo['sheetname']}")
        return None
    if 'chartrules' not in xlsinfo:
        logger.warning(f"not chart rule {xlsinfo['code']} for {xlsinfo['sheetname']}")
        return None
    rule = get_chart_rules(xlsinfo['chartrules'], xlsinfo['code'], xlsinfo['daterange'][0])
    pngname = xlsinfo['excel'].replace('.xlsx', '.png')

    if hasattr(wb, 'sheets'):
        if xlsinfo['fillpicture']:
            sheet = wb.sheets[xlsinfo['sheetname']]
            export_img_from_xw_sheet(sheet, rule, pngname) 
        return fill_word_with_excel_value(wb, xlsinfo)
    
    with Win32AppManager(xlsinfo['excel'], opttype='xw') as (excel, wb):
        if xlsinfo['fillpicture']:
            sheet = wb.sheets[xlsinfo['sheetname']]
            export_img_from_xw_sheet(sheet, rule, pngname) 
        return fill_word_with_excel_value(wb, xlsinfo)
    
def duil_with_file_by_xlwrings(excelpath, maillist, codeinfo):
    renamefile = {}
    wordinfo = []
    with Win32AppManager(excelpath, opttype='xw') as (excel, wb):
        if wb == None:
            logging.error(f"open {excelpath} error")
            return renamefile
        for sheet in wb.sheets:
            xlsinfo = change_xlsinfo_by_xlwrings_sheet(sheet)
            xlsinfo['excel'] = excelpath
            xlsinfo['sheetname'] = sheet.name
            xlsinfo['code'] = maillist[0]['code']
            xlsinfo['filltable'] = True
            xlsinfo['fillpicture'] = True
            logger.info(f"{sheet.name}")
            if sheet.name.find('（日）') > 0:
                for one in maillist:    
                    xlsinfo = deal_with_xlwrings_sheet_data(wb, xlsinfo, one, codeinfo)
                    deal_with_xlwrings_sheet_oldchart(wb, xlsinfo)
                sheet.Visible = False
                if check_code_in_list(xlsinfo['code'], xlsinfo['daterange'][0], xlsinfo['日净值']) == True:
                    xlsinfo['fillpicture'] = False
                    oneword = get_img_from_xw_sheet(wb, xlsinfo)
                    if oneword and oneword not in wordinfo:
                        wordinfo.append(oneword)
            elif sheet.name.find('（周）') > 0:
                weekend = []
                for one in maillist:    
                    if is_weekend_day(one['date']) == False:
                        continue
                    weekend.append(one)
                
                for one in weekend:
                    xlsinfo = deal_with_xlwrings_sheet_data(wb, xlsinfo, one, codeinfo)
                    deal_with_xlwrings_sheet_oldchart(wb, xlsinfo)
                if len(weekend) == 0 and check_code_in_list(xlsinfo['code'], xlsinfo['daterange'][0], xlsinfo['日净值']) == False:
                    continue
                # 日净值的情况要更新周图
                if check_code_in_list(xlsinfo['code'], xlsinfo['daterange'][0], xlsinfo['日净值']) == True:
                    xlsinfo['filltable'] = False
                oneword = get_img_from_xw_sheet(wb, xlsinfo)
                if oneword and oneword not in wordinfo:
                    wordinfo.append(oneword)
            else:
                continue
            renamefile = excel_rename_list_update(xlsinfo, renamefile)

    for oneword in wordinfo:
        if len(oneword) != 3:
            continue
        word_to_image(oneword[0], oneword[1])
        if oneword[2] == False:
            os.remove(oneword[0])
    return renamefile
